import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历_省内就业和省外就业的占比分布(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历，省内就业和省外就业的占比分布 - 指标计算函数
    
    ## 指标说明
    计算各学历层次下，毕业生在省内就业和省外就业的人数及占比分布。
    指标用于分析毕业生就业地域分布情况，为就业指导提供数据支持。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        education (Optional[str]): 学历筛选条件，可选值：本科毕业生/专科毕业生/硕士研究生/博士研究生
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "education": "本科毕业生"
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "group": "本科毕业生",
                "data": [
                    {
                        "type": "省内就业",
                        "count": 1200,
                        "ratio": 0.65
                    },
                    {
                        "type": "省外就业",
                        "count": 650,
                        "ratio": 0.35
                    }
                ],
                "total": 1850
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历，省内就业和省外就业的占比分布, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM dp_njc.client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 构建学历筛选条件
        education_condition = ""
        if education:
            if education not in ["本科毕业生", "专科毕业生", "硕士研究生", "博士研究生"]:
                raise ValueError("学历参数必须是：本科毕业生/专科毕业生/硕士研究生/博士研究生")
            education_condition = f"AND education = '{education}'"
        
        # 4. 查询各学历省内/省外就业分布
        result = []
        
        # 查询总体数据
        total_sql = f"""
        SELECT 
            job_around, 
            COUNT(*) as count,
            COUNT(*)/(SELECT COUNT(*) 
                     FROM dim_client_target_baseinfo_student_calc_{item_year} 
                     WHERE shard_tb_key = %s 
                     AND job_around != '' 
                     {education_condition}) as ratio
        FROM dim_client_target_baseinfo_student_calc_{item_year}
        WHERE shard_tb_key = %s
        AND job_around != ''
        {education_condition}
        GROUP BY job_around
        """
        total_params = (shard_tb_key, shard_tb_key)
        total_results = db.fetchall(total_sql, total_params)
        
        if not total_results:
            raise ValueError("未查询到就业分布数据")
            
        # 处理查询结果
        total_count = sum([res['count'] for res in total_results])
        total_data = {
            "group": "总体" if not education else education,
            "data": [],
            "total": total_count
        }
        
        for res in total_results:
            total_data["data"].append({
                "type": "省内就业" if res['job_around'] == '1' else "省外就业",
                "count": res['count'],
                "ratio": float(res['ratio'])
            })
        
        result.append(total_data)
        
        # 如果不指定学历，则查询各学历的分布情况
        if not education:
            education_sql = f"""
            SELECT 
                education,
                job_around, 
                COUNT(*) as count,
                COUNT(*)/(SELECT COUNT(*) 
                         FROM dim_client_target_baseinfo_student_calc_{item_year} 
                         WHERE shard_tb_key = %s 
                         AND job_around != '' 
                         AND education = t.education) as ratio
            FROM dim_client_target_baseinfo_student_calc_{item_year} t
            WHERE shard_tb_key = %s
            AND job_around != ''
            AND education IN ('本科毕业生', '专科毕业生', '硕士研究生', '博士研究生')
            GROUP BY education, job_around
            """
            education_params = (shard_tb_key, shard_tb_key)
            education_results = db.fetchall(education_sql, education_params)
            
            if education_results:
                # 按学历分组处理结果
                education_groups = {}
                for res in education_results:
                    if res['education'] not in education_groups:
                        education_groups[res['education']] = {
                            "group": res['education'],
                            "data": [],
                            "total": 0
                        }
                    education_groups[res['education']]["data"].append({
                        "type": "省内就业" if res['job_around'] == '1' else "省外就业",
                        "count": res['count'],
                        "ratio": float(res['ratio'])
                    })
                
                # 计算各学历总数
                for edu in education_groups:
                    education_groups[edu]["total"] = sum(
                        [item['count'] for item in education_groups[edu]["data"]]
                    )
                
                result.extend(list(education_groups.values()))

        logger.info(f"指标 '各学历，省内就业和省外就业的占比分布' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历，省内就业和省外就业的占比分布' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历，省内就业和省外就业的占比分布",
            "code": 500,
            "error": str(e)
        }